﻿using MySql.Data.MySqlClient;
using sqlmanage.common;
using System;
using System.Collections;
using System.Data;
using System.Diagnostics;
using System.IO;
using System.Text;
using System.Windows.Forms;
using System.Xml;

public partial class mainform : Form
{
    public string str_type = "";

    /// <summary>
    ///
    /// </summary>
    public mainform()
    {
        InitializeComponent();
    }

    /// <summary>
    ///
    /// </summary>
    /// <param name="args"></param>
    public mainform(string[] args)
    {
        InitializeComponent();

        //string s1 = "";
        //foreach (string s in args)
        //{
        //    s1 = s1 + s + " ";
        //}

        //string sql = s1;
        //connect_database();
        //this.tabControlMain.SelectedTab = this.tabPage2;
        //this.richTextBox1.Text = sql;

        //ExcuteSql(sql);
    }

    /// <summary>
    /// 连接数据库
    /// </summary>
    private void connect_database()
    {
        try
        {
            if (this.textBoxServer.Text == "") { MessageBox.Show("server非空"); return; }
            if (this.textBox_type.Text == "") { MessageBox.Show("type非空"); return; }

            //*********************************************************
            //
            //*********************************************************
            //清除treeview
            treeViewOne.Nodes.Clear();
            TreeNode tn1 = new TreeNode();
            tn1.Text = this.textBoxServer.Text.ToString();
            tn1.Tag = "root";

            tn1.ImageIndex = 0;
            tn1.SelectedImageIndex = 1;

            treeViewOne.Nodes.Add(tn1);

            //*********************************************************
            //
            //*********************************************************
            string datasource = this.textBoxServer.Text;
            //string database = this.textBoxDataBase.Text;
            string uid = this.textBoxUid.Text;
            string pwd = this.textBoxPass.Text;
            string type = this.textBox_type.Text;
            str_type = this.textBox_type.Text;
            string shujuku = this.textBoxshujuku.Text;

            if (type == "mysql")
            {
                this.dboone = dbofactory.getdbo(type);
                Hashtable hashtable = new Hashtable();
                hashtable["server"] = datasource;
                hashtable["user"] = uid;
                hashtable["password"] = pwd;
                hashtable["treeView"] = this.treeViewOne;
                hashtable["dataGridView"] = this.dataGridViewOne;

                dboone.connect_database(hashtable);
            }
            else if (type == "sqlserver")
            {
                this.dboone = dbofactory.getdbo(type);

                Hashtable hashtable = new Hashtable();
                hashtable["server"] = datasource;
                hashtable["user"] = uid;
                hashtable["password"] = pwd;
                hashtable["treeView"] = this.treeViewOne;
                hashtable["dataGridView"] = this.dataGridViewOne;
                hashtable["shujuku"] = shujuku;

                dboone.connect_database(hashtable);
            }
        }
        catch (Exception ee)
        {
            MessageBox.Show("连接失败....");
        }
    }

    /// <summary>
    /// 连接
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void buttonTestConect_Click(object sender, EventArgs e)
    {
        connect_database();
    }

    private void BindTree()
    {
    }

    /// <summary>
    /// 加载
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void 综合_Load(object sender, EventArgs e)
    {
        loadxml();

        this.treeViewOne.ImageList = this.imageList1;
    }

    private dbo dboone = null;

    private void loadxml()
    {
        this.dataGridView1.Rows.Clear();

        //string result = "";

        string strPath = Application.StartupPath + "\\Config" + "\\sql.xml";
        XmlDocument doc = new XmlDocument();
        doc.Load(strPath);

        XmlNodeList nodeList = doc.SelectSingleNode("root").ChildNodes;
       

        this.dataGridView1.Columns.Add("id", "id");
        this.dataGridView1.Columns.Add("type", "服务器类型");
        this.dataGridView1.Columns.Add("server", "服务器");
        this.dataGridView1.Columns.Add("user", "用户名");
        this.dataGridView1.Columns.Add("password", "密码");
        this.dataGridView1.Columns.Add("db", "数据库");

        int i = 1;
        foreach (XmlNode xn in nodeList)
        {
            string server = "";
            string name = "";
            string user = "";
            string password = "";
            string shujuku = "";

            name = xn.Name;
            if (xn.Attributes["server"] != null)
                server = xn.Attributes["server"].FirstChild.Value;
            if (xn.Attributes["user"] != null)
                user = xn.Attributes["user"].FirstChild.Value;
            if (xn.Attributes["password"] != null)
                password = xn.Attributes["password"].FirstChild.Value;
            if (xn.Attributes["shujuku"] != null)
                shujuku = xn.Attributes["shujuku"].FirstChild.Value;

            this.dataGridView1.Rows.Add(i.ToString(), name.Trim(), server.Trim(), user.Trim(), password.Trim(), shujuku.Trim());
            i++;
        }
    }

    /// <summary>
    /// listbox
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    //private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
    //{
    //    try
    //    {
    //        if (listBoxdatabase.SelectedItem != null)
    //        {
    //            string result = listBoxdatabase.SelectedItem.ToString();
    //            string[] result1 = result.Split(';');

    //            this.textBoxServer.Text = result1[1].Replace("server:", "").Trim();
    //            this.textBoxUid.Text = result1[2].Replace("user:", "").Trim();
    //            this.textBoxPass.Text = result1[3].Replace("password:", "").Trim();
    //            this.textBox_type.Text = result1[0].Replace("type:", "").Trim();
    //            this.textBoxshujuku.Text = result1[4].Replace("shujuku:", "").Trim();
    //            str_type = this.textBox_type.Text;
    //        }
    //    }
    //    catch (Exception ee)
    //    {
    //    }
    //}

    /// <summary>
    /// 点击树
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void treeView1_AfterSelect(object sender, TreeViewEventArgs e)
    {
        try
        {
            if (this.treeViewOne.SelectedNode.Tag.ToString() == "root")
            {
                treeViewOne.SelectedNode.Expand();
                return;
            }

            treeViewOne.SelectedNode.Nodes.Clear();

            //*********************************************************
            //数据库
            //*********************************************************
            if (this.treeViewOne.SelectedNode.Tag.ToString() == "database")
            {
                if (str_type == "mysql")
                {
                    string strSql = "show tables from " + treeViewOne.SelectedNode.Text;

                    MySqlDataReader thisReader = MysqlHelper.GetReader(strSql);
                    while (thisReader.Read())
                    {
                        TreeNode tn = new TreeNode();
                        tn.Text = thisReader[0].ToString();
                        tn.Tag = "table";
                        tn.ImageIndex = 4;
                        tn.SelectedImageIndex = 5;

                        treeViewOne.SelectedNode.Nodes.Add(tn);
                        treeViewOne.SelectedNode.Expand();
                    }

                    thisReader.Close();
                }
                else if (str_type == "sqlserver")
                {
                    string strSql = "use " + treeViewOne.SelectedNode.Text + ";" + "select * from sysobjects where type='U' and name<>'sysdiagrams'";

                    System.Data.SqlClient.SqlDataReader thisReader = SqlServerHelper.GetReader(strSql);
                    while (thisReader.Read())
                    {
                        TreeNode tn = new TreeNode();
                        tn.Text = thisReader[0].ToString();
                        tn.Tag = "table";
                        tn.ImageIndex = 4;
                        tn.SelectedImageIndex = 5;

                        treeViewOne.SelectedNode.Nodes.Add(tn);
                    }
                    treeViewOne.SelectedNode.Expand();
                    treeViewOne.Sort();

                    thisReader.Close();
                }
            }

            //*********************************************************
            //表
            //*********************************************************
            else if (this.treeViewOne.SelectedNode.Tag.ToString() == "table")
            {
                if (str_type == "mysql")
                {
                    //*********************************************************
                    //
                    //*********************************************************
                    string strDataBase = this.treeViewOne.SelectedNode.Parent.Text;
                    string strTable = this.treeViewOne.SelectedNode.Text;
                    string strSql = "select * from " + strDataBase + "." + strTable + " limit 0,100";

                    this.dataGridViewTwo.DataSource = MysqlHelper.GetDataTable(strSql);

                    //*********************************************************
                    //SHOW full COLUMNS FROM assets.device
                    //*********************************************************
                    //strSql = "SHOW COLUMNS FROM " + strDataBase + "." + strTable + "";
                    strSql = "SHOW full COLUMNS FROM " + strDataBase + "." + strTable + "";
                    DataTable dt = MysqlHelper.GetDataTable(strSql);
                    foreach (DataRow dr in dt.Rows)
                    {
                        string field = dr["field"].ToString();
                        string type = dr["type"].ToString();
                        string nullvalue = dr["null"].ToString();
                        string key = dr["key"].ToString();
                        string extra = dr["extra"].ToString();
                        string comment = dr["comment"].ToString();

                        string result = "";
                        result += field;
                        result += " [";
                        result += type + ",";

                        if (nullvalue.ToLower() == "yes")
                        {
                            result += "null" + ",";
                        }
                        else
                        {
                            result += "not null" + ",";
                        }

                        result += key + ",";

                        result += extra + ",";

                        result += comment + "]";

                        TreeNode tn = new TreeNode();
                        tn.Text = result;
                        tn.Tag = "column";
                        tn.ImageIndex = 6;
                        tn.SelectedImageIndex = 7;
                        treeViewOne.SelectedNode.Nodes.Add(tn);
                    }
                }
                else if (str_type == "sqlserver")
                {
                    //*********************************************************
                    //
                    //*********************************************************
                    string strDataBase = this.treeViewOne.SelectedNode.Parent.Text;
                    string strTable = this.treeViewOne.SelectedNode.Text;
                    string strSql = "select top 10 * from " + strDataBase + ".." + strTable + "";

                    //var table= SqlServerHelper.getDataTable(strSql);
                    //foreach (DataColumnCollection item in table.Columns)
                    //{
                    //    //if(item.GetType()==)
                    //}

                    this.dataGridViewTwo.DataSource = SqlServerHelper.getDataTable(strSql);

                    //*********************************************************
                    //SHOW full COLUMNS FROM assets.device
                    //*********************************************************
                    //strSql = "SHOW COLUMNS FROM " + strDataBase + "." + strTable + "";
                    //strSql = "select   *   from   syscolumns where id=object_id('" + strDataBase + ".." + strTable + "')" ;
                    strSql = "use " + strDataBase + ";select   a.[name] as lieming,b.[name] as leixing    from   syscolumns a,systypes b  where   a.id=object_id('"
                        + strDataBase + ".." + strTable + "') and a.xtype=b.xtype and    b.[name]<>'sysname'";

                    DataTable dt = SqlServerHelper.getDataTable(strSql);
                    foreach (DataRow dr in dt.Rows)
                    {
                        string field = dr["lieming"].ToString();
                        string type = dr["leixing"].ToString();
                        //string nullvalue = dr["null"].ToString();
                        //string key = dr["key"].ToString();
                        //string extra = dr["extra"].ToString();
                        //string comment = dr["comment"].ToString();

                        string result = "";
                        result += field;
                        result += " [";
                        result += type + "]";

                        TreeNode tn = new TreeNode();
                        tn.Text = result;
                        tn.Tag = "column";
                        tn.ImageIndex = 6;
                        tn.SelectedImageIndex = 7;
                        treeViewOne.SelectedNode.Nodes.Add(tn);
                    }
                }
            }
        }
        catch (Exception ee)
        {
        }
    }

    private void ExcuteSql(string strSql)
    {
        try
        {
            DataTable dt = dboone.ExcuteSql(strSql + " select @@ROWCOUNT as 行受影响 ");

            this.dataGridViewTwo.DataSource = dt;
            //this.tabControlexcute.SelectedTab = tabPageResult;

            //string sql = "select @@ROWCOUNT  ";

            //var s = dboone.ExecuteAlone(sql);
            //richTextBoxMessage.Text = "(" + s.ToString() + " 行受影响)";
        }
        catch (Exception ee)
        {
            this.tabControlexcute.SelectedTab = tabPageMessage;
            richTextBoxMessage.Text = ee.ToString();
            this.dataGridViewTwo.DataSource = null;
        }
    }

    /// <summary>
    ///执行
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void toolStripLabelexcute_Click(object sender, EventArgs e)
    {
        sqlrun();
    }

    private void sqlrun()
    {
        if (this.richTextBoxexcute.SelectedText == "")
        {
            string strSql = richTextBoxexcute.Text;

            ExcuteSql(strSql);
        }
        else
        {
            string strSql = richTextBoxexcute.SelectedText;

            ExcuteSql(strSql);
        }
    }

    private void richTextBox1_KeyPress(object sender, KeyPressEventArgs e)
    {
        //if(e.KeyChar)
    }

    private void richTextBox1_KeyDown(object sender, KeyEventArgs e)
    {
        if (e.KeyCode.ToString() == "F5")
        {
            if (this.richTextBoxexcute.SelectedText == "")
            {
                string strSql = richTextBoxexcute.Text;

                ExcuteSql(strSql);
            }
            else
            {
                string strSql = richTextBoxexcute.SelectedText;

                ExcuteSql(strSql);
            }
        }
    }

    /// <summary>
    /// 导出结构
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void toolStripLabel2_Click(object sender, EventArgs e)
    {
        if (this.treeViewOne.SelectedNode.Tag.ToString() == "table")
        {
            StringBuilder strTrans = new StringBuilder();

            string database = treeViewOne.SelectedNode.Parent.Text;
            string table = treeViewOne.SelectedNode.Text;

            strTrans.Append("SET NAMES utf8;\n");
            strTrans.Append("\n");

            strTrans.Append("create database if not exists`" + database + "`;\n");
            strTrans.Append("\n");
            strTrans.Append("USE`" + database + "`;\n");
            strTrans.Append("\n");

            strTrans.Append("/*Table structure for table`" + table + "` */\n");
            strTrans.Append("\n");
            strTrans.Append("DROP TABLE IF EXISTS`" + table + "`;\n");

            strTrans.Append("CREATE TABLE`" + table + "` (\n");

            string result = "";
            string strSql = "SHOW full COLUMNS FROM " + database + "." + table + "";
            DataTable dt = MysqlHelper.GetDataTable(strSql);
            foreach (DataRow dr in dt.Rows)
            {
                string field = dr["field"].ToString();
                string type = dr["type"].ToString();
                string nullvalue = dr["null"].ToString();
                string key = dr["key"].ToString();
                string extra = dr["extra"].ToString();
                string comment = dr["comment"].ToString();

                result += "`" + field + "`";
                result += " ";
                result += type + " ";

                if (nullvalue.ToLower() == "yes")
                {
                    result += "null" + " ";
                }
                else
                {
                    result += "not null" + " ";
                }

                if (key.ToLower() == "pri")
                {
                    result += " PRIMARY KEY " + " ";
                }
                else
                {
                }

                result += "COMMENT '" + extra + "', \n";
            }

            result = result.Substring(0, result.LastIndexOf(","));
            strTrans.Append(result);

            strTrans.Append(" \n");
            strTrans.Append(")  DEFAULT CHARSET=latin1;\n");

            string strDes = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            strDes = strDes.Replace(@"\\", @"\");
            strDes = Path.Combine(strDes, "1.sql");

            Encoding encode = Encoding.UTF8;
            StreamWriter sw = new StreamWriter(strDes, false, encode);
            sw.Write(strTrans.ToString());
            sw.Flush();
            sw.Close();

            Process.Start(strDes);
        }
    }

    /// <summary>
    /// 导出
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void toolStripButton1_Click(object sender, EventArgs e)
    {
        if (this.treeViewOne.SelectedNode.Tag.ToString() == "table")
        {
            //*********************************************************
            //mysql
            //*********************************************************
            if (str_type == "mysql")
            {
                StringBuilder strTrans = new StringBuilder();

                string database = treeViewOne.SelectedNode.Parent.Text;
                string table = treeViewOne.SelectedNode.Text;

                //*********************************************************
                //jianku
                //*********************************************************
                strTrans.Append("SET NAMES utf8;\n");
                strTrans.Append("\n");

                strTrans.Append("create database if not exists`" + database + "`;\n");
                strTrans.Append("\n");
                strTrans.Append("USE`" + database + "`;\n");
                strTrans.Append("\n");

                strTrans.Append("/*Table structure for table`" + table + "` */\n");
                strTrans.Append("\n");
                strTrans.Append("DROP TABLE IF EXISTS`" + table + "`;\n");

                strTrans.Append("CREATE TABLE`" + table + "` (\n");

                string result = "";
                string strSql = "SHOW full COLUMNS FROM " + database + "." + table + "";
                DataTable dt = MysqlHelper.GetDataTable(strSql);
                foreach (DataRow dr in dt.Rows)
                {
                    string field = dr["field"].ToString();
                    string type = dr["type"].ToString();
                    string nullvalue = dr["null"].ToString();
                    string key = dr["key"].ToString();
                    string extra = dr["extra"].ToString();
                    string comment = dr["comment"].ToString();

                    result += "`" + field + "`";
                    result += " ";
                    result += type + " ";

                    if (nullvalue.ToLower() == "yes")
                    {
                        result += "null" + " ";
                    }
                    else
                    {
                        result += "not null" + " ";
                    }

                    if (key.ToLower() == "pri")
                    {
                        result += " PRIMARY KEY " + " ";
                    }
                    else
                    {
                    }

                    result += "COMMENT '" + extra + "', \n";
                }

                result = result.Substring(0, result.LastIndexOf(","));
                strTrans.Append(result);

                strTrans.Append(" \n");
                strTrans.Append(")  DEFAULT CHARSET=latin1;\n");

                strTrans.Append("\n");
                strTrans.Append("/*Data for the table `student` */\n");

                //*********************************************************
                //cha ru zi duan
                //*********************************************************
                strSql = "select * FROM " + database + "." + table + "";
                dt = MysqlHelper.GetDataTable(strSql);

                string strFields = "";
                string strValues = "";

                foreach (DataColumn dc in dt.Columns)
                {
                    if (dc.AutoIncrement != true)
                    {
                        strFields += "`" + dc.ColumnName + "`,";
                    }
                }

                foreach (DataRow dr in dt.Rows)
                {
                    string strTemp = "";
                    foreach (DataColumn dc in dt.Columns)
                    {
                        if (dc.AutoIncrement != true)
                        {
                            strTemp += "'" + dr[dc.ColumnName].ToString() + "',";
                        }
                    }

                    strValues += "(" + strTemp.Substring(0, strTemp.Length - 1) + ")" + ",";
                }

                strFields = strFields.Substring(0, strFields.Length - 1);
                strValues = strValues.Substring(0, strValues.Length - 1) + ";";
                strTrans.Append("insert into `" + database + "`.`" + table + "` (" + strFields + ") values " + strValues + "\n");

                strTrans.Append("\n");

                //*********************************************************
                //xie wen jian
                //*********************************************************
                string strDes = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
                strDes = strDes.Replace(@"\\", @"\");
                strDes = Path.Combine(strDes, "1.sql");

                Encoding encode = Encoding.UTF8;
                StreamWriter sw = new StreamWriter(strDes, false, encode);
                sw.Write(strTrans.ToString());
                sw.Flush();
                sw.Close();

                Process.Start(strDes);
            }
            //*********************************************************
            //
            //*********************************************************
            else if (str_type == "sqlserver")
            {
                StringBuilder strTrans = new StringBuilder();

                string database = treeViewOne.SelectedNode.Parent.Text;
                string table = treeViewOne.SelectedNode.Text;

                //*********************************************************
                //jianku
                //*********************************************************
                strTrans.Append("USE`" + database + "`;\n");
                strTrans.Append("\n");

                strTrans.Append("/*Table structure for table`" + table + "` */\n");
                strTrans.Append("\n");
                strTrans.Append("DROP TABLE " + table + ";\n");

                strTrans.Append("CREATE TABLE " + table + " (\n");

                string result = "";

                //string strSql = "SHOW full COLUMNS FROM " + database + "." + table + "";
                string strSql = "";
                strSql = "use " + database + ";select   a.[name] as lieming,b.[name] as leixing    from   syscolumns a,systypes b  where   a.id=object_id('" + database + ".." + table + "') and a.xtype=b.xtype and    b.[name]<>'sysname'";

                DataTable dt = SqlServerHelper.getDataTable(strSql);
                foreach (DataRow dr in dt.Rows)
                {
                    string field = dr["lieming"].ToString();
                    string type = dr["leixing"].ToString();
                    //string nullvalue = dr["null"].ToString();
                    //string key = dr["key"].ToString();
                    //string extra = dr["extra"].ToString();
                    //string comment = dr["comment"].ToString();

                    result += "" + field + "";
                    result += " ";
                    result += type + " ,";

                    //if (nullvalue.ToLower() == "yes")
                    //{
                    //    result += "null" + " ";
                    //}
                    //else
                    //{
                    //    result += "not null" + " ";
                    //}

                    //if (key.ToLower() == "pri")
                    //{
                    //    result += " PRIMARY KEY " + " ";
                    //}
                    //else
                    //{
                    //}

                    //result += "COMMENT '" + extra + "', \n";
                }

                result = result.Substring(0, result.LastIndexOf(","));
                strTrans.Append(result);

                strTrans.Append(" \n");
                strTrans.Append(")  \n");

                strTrans.Append("\n");
                strTrans.Append("/*Data for the table `student` */\n");

                //*********************************************************
                //cha ru zi duan
                //*********************************************************
                strSql = "select * FROM " + database + ".." + table + "";
                dt = SqlServerHelper.getDataTable(strSql);

                string strFields = "";
                string strValues = "";

                foreach (DataColumn dc in dt.Columns)
                {
                    if (dc.AutoIncrement != true)
                    {
                        strFields += "" + dc.ColumnName + ",";
                    }
                }

                foreach (DataRow dr in dt.Rows)
                {
                    string strTemp = "";
                    foreach (DataColumn dc in dt.Columns)
                    {
                        //if (dc.AutoIncrement != true)
                        //{
                        //    strTemp += "'" + dr[dc.ColumnName].ToString() + "',";
                        //}
                        strTemp += "'" + dr[dc.ColumnName].ToString() + "',";
                    }

                    strValues += "(" + strTemp.Substring(0, strTemp.Length - 1) + ")" + ",";
                }

                strFields = strFields.Substring(0, strFields.Length - 1);
                strValues = strValues.Substring(0, strValues.Length - 1) + ";";
                strTrans.Append("insert into " + database + ".." + table + " (" + strFields + ") values " + strValues + "\n");

                strTrans.Append("\n");

                //*********************************************************
                //xie wen jian
                //*********************************************************
                string strDes = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
                strDes = strDes.Replace(@"\\", @"\");
                strDes = Path.Combine(strDes, "1.sql");

                Encoding encode = Encoding.UTF8;
                StreamWriter sw = new StreamWriter(strDes, false, encode);
                sw.Write(strTrans.ToString());
                sw.Flush();
                sw.Close();

                Process.Start(strDes);
            }
        }
    }

    /// <summary>
    /// 导入
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void toolStripLabel3_Click(object sender, EventArgs e)
    {
        string strDes = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
        strDes = strDes.Replace(@"\\", @"\");
        strDes = Path.Combine(strDes, "1.sql");

        Encoding encode = Encoding.UTF8;
        StreamReader sw = new StreamReader(strDes);

        string strResult = sw.ReadToEnd();
        sw.Close();

        ExcuteSql(strResult);

        MessageBox.Show("ok");
    }

    /// <summary>
    ///add
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void toolStripButtonAdd_Click(object sender, EventArgs e)
    {
        //this.tabControlexcute.SelectedTab = tabPageMessage;
        //insert into grade(学号，姓名，数学) values(123,"周润发"，70)

        add();
    }

    private void add()
    {
        StringBuilder strTrans = new StringBuilder();
        this.richTextBoxexcute.Text = "";
        if (this.treeViewOne.SelectedNode == null || this.treeViewOne.SelectedNode.Tag.ToString() != "table")
        {
            strTrans.Append("insert into grade(学号，姓名，数学) values(123,\"周润发\"，70)\n");
            this.richTextBoxexcute.Text = strTrans.ToString();
            return;
        }
        else
        {
            string database = this.treeViewOne.SelectedNode.Parent.Text;
            string table = this.treeViewOne.SelectedNode.Text;
            strTrans = dboone.Create(database, table);

            this.richTextBoxexcute.Text = strTrans.ToString();
            return;
        }
    }

    /// <summary>
    /// delete
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void toolStripButton3_Click(object sender, EventArgs e)
    {
        delete();
    }

    private void delete()
    {
        //delete from grade where 数学＝0

        //this.tabControlexcute.SelectedTab = tabPageMessage;

        StringBuilder strTrans = new StringBuilder();

        this.richTextBoxexcute.Text = "";

        if (this.treeViewOne.SelectedNode == null || this.treeViewOne.SelectedNode.Tag != "table")
        {
            strTrans.Append("delete from grade where 数学＝0");
            this.richTextBoxexcute.Text = strTrans.ToString();
            return;
        }
        else
        {
            string database = this.treeViewOne.SelectedNode.Parent.Text;
            string table = this.treeViewOne.SelectedNode.Text;

            if (str_type == "mysql")
            {
                strTrans.Append("delete from `" + database + "`.`" + table + "`");
                this.richTextBoxexcute.Text = strTrans.ToString();
                return;
            }
            else if (str_type == "sqlserver")
            {
                strTrans.Append("delete from " + database + ".." + table + "");
                this.richTextBoxexcute.Text = strTrans.ToString();
                return;
            }
        }
    }

    /// <summary>
    /// update
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void toolStripButton4_Click(object sender, EventArgs e)
    {
        modify();
    }

    private void modify()
    {
        //update grade set 数学＝数学+10
        //this.tabControlexcute.SelectedTab = tabPageMessage;
        StringBuilder strTrans = new StringBuilder();

        this.richTextBoxexcute.Text = "";
        if (this.treeViewOne.SelectedNode == null || this.treeViewOne.SelectedNode.Tag != "table")
        {
            strTrans.Append("update grade set 数学＝数学+10 where id=1\n");
            this.richTextBoxexcute.Text = strTrans.ToString();
            return;
        }
        else
        {
            string database = this.treeViewOne.SelectedNode.Parent.Text;
            string table = this.treeViewOne.SelectedNode.Text;

            //string strSql = "select * FROM " + database + "." + table + "";
            //DataTable dt = MysqlHelper.GetDataTable(strSql);
            if (str_type == "mysql")
            {
                string strSql = "SHOW full COLUMNS FROM " + database + "." + table + "";
                DataTable dt = MysqlHelper.GetDataTable(strSql);

                string strFields = "";
                string strValues = "";

                foreach (DataRow dr in dt.Rows)
                {
                    string field = dr["field"].ToString();
                    string type = dr["type"].ToString();
                    string nullvalue = dr["null"].ToString();
                    string key = dr["key"].ToString();
                    string extra = dr["extra"].ToString();
                    string comment = dr["comment"].ToString();

                    if (extra.IndexOf("auto_increment") != -1)
                    {
                        continue;
                    }

                    strFields += "`" + field + "`='1',";
                }

                strFields = strFields.Substring(0, strFields.Length - 1);
                //strValues = strValues.Substring(0, strValues.Length - 1);

                strTrans.Append("update  `" + database + "`.`" + table + "`set " + strFields + "");
                this.richTextBoxexcute.Text = strTrans.ToString();
                return;
            }
            else if (str_type == "sqlserver")
            {
                string strSql = "use " + database + ";select * from sys.columns where object_id = object_id('" + table + "')";

                DataTable dt = SqlServerHelper.getDataTable(strSql);

                string strFields = "";
                string strValues = "";

                foreach (DataRow dr in dt.Rows)
                {
                    string field = dr["name"].ToString();
                    //string type = dr["type"].ToString();
                    //string nullvalue = dr["null"].ToString();
                    //string key = dr["key"].ToString();
                    //string extra = dr["extra"].ToString();
                    //string comment = dr["comment"].ToString();

                    //if (extra.IndexOf("auto_increment") != -1)
                    //{
                    //    continue;
                    //}

                    strFields += "" + field + "='1',";
                }

                strFields = strFields.Substring(0, strFields.Length - 1);
                //strValues = strValues.Substring(0, strValues.Length - 1);

                strTrans.Append("update  " + database + ".." + table + " set " + strFields + "");
                this.richTextBoxexcute.Text = strTrans.ToString();
                return;
            }
        }
    }

    /// <summary>
    /// select
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void toolStripButton5_Click(object sender, EventArgs e)
    {
        read();
    }

    private void read()
    {
        //this.tabControlexcute.SelectedTab = tabPageResult;
        //update grade set 数学＝数学+10

        StringBuilder strTrans = new StringBuilder();

        this.richTextBoxexcute.Text = "";
        if (this.treeViewOne.SelectedNode == null || this.treeViewOne.SelectedNode.Tag.ToString() != "table")
        {
            strTrans.Append("select * from sys.sysobjects   where id=1\n");
            this.richTextBoxexcute.Text = strTrans.ToString();
            return;
        }
        else
        {
            string database = this.treeViewOne.SelectedNode.Parent.Text;
            string table = this.treeViewOne.SelectedNode.Text;

            if (str_type == "mysql")
            {
                //string strSql = "select * FROM " + database + "." + table + "";
                //DataTable dt = MysqlHelper.GetDataTable(strSql);

                string strSql = "SHOW full COLUMNS FROM " + database + "." + table + "";
                DataTable dt = MysqlHelper.GetDataTable(strSql);

                string strFields = "";
                string strValues = "";

                foreach (DataRow dr in dt.Rows)
                {
                    string field = dr["field"].ToString();
                    string type = dr["type"].ToString();
                    string nullvalue = dr["null"].ToString();
                    string key = dr["key"].ToString();
                    string extra = dr["extra"].ToString();
                    string comment = dr["comment"].ToString();

                    //if (extra.IndexOf("auto_increment") != -1)
                    //{
                    //    continue;
                    //}

                    strFields += "`" + field + "`,";
                }

                strFields = strFields.Substring(0, strFields.Length - 1);
                //strValues = strValues.Substring(0, strValues.Length - 1);

                strTrans.Append("select " + strFields + " from `" + database + "`.`" + table + "`");
                this.richTextBoxexcute.Text = strTrans.ToString();
                return;
            }
            else if (str_type == "sqlserver")
            {
                string strSql = "use " + database + ";select * from sys.columns where object_id = object_id('" + table + "')";
                //string strSql = "SHOW full COLUMNS FROM " + database + "." + table + "";
                DataTable dt = SqlServerHelper.getDataTable(strSql);

                string strFields = "";
                string strValues = "";

                foreach (DataRow dr in dt.Rows)
                {
                    string field = dr["name"].ToString();
                    //string type = dr["type"].ToString();
                    //string nullvalue = dr["null"].ToString();
                    //string key = dr["key"].ToString();
                    //string extra = dr["extra"].ToString();
                    //string comment = dr["comment"].ToString();
                    strFields += "" + field + ",";
                }
                if (!string.IsNullOrEmpty(strFields))
                    strFields = strFields.Substring(0, strFields.Length - 1);
                //strValues = strValues.Substring(0, strValues.Length - 1);

                strTrans.Append("select " + strFields + " from " + database + ".." + table + "");
                this.richTextBoxexcute.Text = strTrans.ToString();
                return;
            }
        }
    }

    /// <summary>
    /// alter
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void toolStripButton6_Click(object sender, EventArgs e)
    {
        //alter table table1 add No_id varchar(50)  null
        StringBuilder strTrans = new StringBuilder();

        this.richTextBoxexcute.Text = "";

        if (this.treeViewOne.SelectedNode == null || this.treeViewOne.SelectedNode.Tag != "table")
        {
            strTrans.Append("/*\n");
            strTrans.Append("mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));\n");
            strTrans.Append("把表t1重新命名为t2：\n");
            strTrans.Append("\n");
            strTrans.Append("mysql> ALTER TABLE t1 RENAME t2;\n");
            strTrans.Append("把列a从INTERGER更改为TINYINT NOT NULL（名称保持不变），并把列b从CHAR(10)更改为CHAR(20)，同时把列b重新命名为列c：\n");
            strTrans.Append("\n");
            strTrans.Append("mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);\n");
            strTrans.Append("添加一个新的TIMESTAMP列，名称为d：\n");
            strTrans.Append("\n");
            strTrans.Append("mysql> ALTER TABLE t2 ADD d TIMESTAMP;\n");
            strTrans.Append("在列d和列a中添加索引：\n");
            strTrans.Append("\n");
            strTrans.Append("mysql> ALTER TABLE t2 ADD INDEX (d), ADD INDEX (a);\n");
            strTrans.Append("删除列c：\n");
            strTrans.Append("\n");
            strTrans.Append("mysql> ALTER TABLE t2 DROP COLUMN c;\n");
            strTrans.Append("添加一个新的AUTO_INCREMENT整数列，名称为c：\n");
            strTrans.Append("\n");
            strTrans.Append("mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,\n");
            strTrans.Append("    ->     ADD PRIMARY KEY (c);\n");
            strTrans.Append("*/\n");

            this.richTextBoxexcute.Text = strTrans.ToString();
            return;
        }
        else
        {
            string database = this.treeViewOne.SelectedNode.Parent.Text;
            string table = this.treeViewOne.SelectedNode.Text;

            strTrans.Append("alter  table `" + database + "`.`" + table + "` add No_id varchar(50)  null ");

            strTrans.Append("\n");

            strTrans.Append("/*\n");
            strTrans.Append("mysql> CREATE TABLE t1 (a INTEGER,b CHAR(10));\n");
            strTrans.Append("把表t1重新命名为t2：\n");
            strTrans.Append("\n");
            strTrans.Append("mysql> ALTER TABLE t1 RENAME t2;\n");
            strTrans.Append("把列a从INTERGER更改为TINYINT NOT NULL（名称保持不变），并把列b从CHAR(10)更改为CHAR(20)，同时把列b重新命名为列c：\n");
            strTrans.Append("\n");
            strTrans.Append("mysql> ALTER TABLE t2 MODIFY a TINYINT NOT NULL, CHANGE b c CHAR(20);\n");
            strTrans.Append("添加一个新的TIMESTAMP列，名称为d：\n");
            strTrans.Append("\n");
            strTrans.Append("mysql> ALTER TABLE t2 ADD d TIMESTAMP;\n");
            strTrans.Append("在列d和列a中添加索引：\n");
            strTrans.Append("\n");
            strTrans.Append("mysql> ALTER TABLE t2 ADD INDEX (d), ADD INDEX (a);\n");
            strTrans.Append("删除列c：\n");
            strTrans.Append("\n");
            strTrans.Append("mysql> ALTER TABLE t2 DROP COLUMN c;\n");
            strTrans.Append("添加一个新的AUTO_INCREMENT整数列，名称为c：\n");
            strTrans.Append("\n");
            strTrans.Append("mysql> ALTER TABLE t2 ADD c INT UNSIGNED NOT NULL AUTO_INCREMENT,\n");
            strTrans.Append("    ->     ADD PRIMARY KEY (c);\n");
            strTrans.Append("*/\n");

            this.richTextBoxexcute.Text = strTrans.ToString();
            return;
        }
    }

    /// <summary>
    /// drop
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void toolStripButton7_Click(object sender, EventArgs e)
    {
        //DROP TABLE  `name` ;

        StringBuilder strTrans = new StringBuilder();

        this.richTextBoxexcute.Text = "";

        if (this.treeViewOne.SelectedNode == null || this.treeViewOne.SelectedNode.Tag != "table")
        {
            strTrans.Append("DROP TABLE  `name` ; ");
            this.richTextBoxexcute.Text = strTrans.ToString();
            return;
        }
        else
        {
            string database = this.treeViewOne.SelectedNode.Parent.Text;
            string table = this.treeViewOne.SelectedNode.Text;

            strTrans.Append("DROP TABLE   `" + database + "`.`" + table + "`; ");

            strTrans.Append("\n");

            this.richTextBoxexcute.Text = strTrans.ToString();
            return;
        }
    }

    private void 综合_MouseClick(object sender, MouseEventArgs e)
    {
    }

    private void treeView1_Click(object sender, EventArgs e)
    {
    }

    private void treeView1_MouseClick(object sender, MouseEventArgs e)
    {
        try
        {
            if (e.Button.ToString() == "Right")
            {
                string strTemp = this.treeViewOne.SelectedNode.Text;

                string strTextBox = richTextBoxexcute.Text;
                string strResult = "";

                int i = this.richTextBoxexcute.SelectionStart;
                strResult = strTextBox.Substring(0, i);
                strResult += strTemp;
                strResult += strTextBox.Substring(i, strTextBox.Length - i);

                richTextBoxexcute.Text = strResult;
                richTextBoxexcute.SelectionStart = i;
            }
        }
        catch (Exception ee) { }
    }

    /// <summary>
    /// create
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void toolStripButton8_Click(object sender, EventArgs e)
    {
        StringBuilder strTrans = new StringBuilder();

        strTrans.Append("USE  `test`;\n");
        strTrans.Append("\n");
        strTrans.Append("/*Table structure for table`member` */\n");
        strTrans.Append("\n");
        strTrans.Append("DROP TABLE IF EXISTS`member`;\n");
        strTrans.Append("CREATE TABLE`member` (\n");
        strTrans.Append("`id` int(11) not null  PRIMARY KEY  AUTO_INCREMENT COMMENT '', \n");
        strTrans.Append("`name` varchar(50) null COMMENT '', \n");
        strTrans.Append("`pass` varchar(50) null COMMENT '', \n");
        strTrans.Append("`rank` int(11) null COMMENT '', \n");
        strTrans.Append("`status` int(1) null COMMENT '' \n");
        strTrans.Append(")  DEFAULT CHARSET=latin1;\n");
        strTrans.Append("\n");
        strTrans.Append("\n");
        strTrans.Append("/*\n");
        strTrans.Append("int(10)\n");
        strTrans.Append("text\n");
        strTrans.Append("varchar(30)\n");
        strTrans.Append("datetime\n");
        strTrans.Append("*/\n");

        this.richTextBoxexcute.Text = strTrans.ToString();
        return;
    }

    private void toolStripButton9_Click(object sender, EventArgs e)
    {
        StringBuilder strTrans = new StringBuilder();
        this.richTextBoxexcute.Text = "";

        if (this.treeViewOne.SelectedNode == null || this.treeViewOne.SelectedNode.Tag != "table")
        {
            strTrans.Append("insert into table_detail(table_name,`field`,name) values(123,'周润发',70)\n");
            this.richTextBoxexcute.Text = strTrans.ToString();
            return;
        }
        else
        {
            string database = this.treeViewOne.SelectedNode.Parent.Text;
            string table = this.treeViewOne.SelectedNode.Text;

            strTrans.Append("use " + database + ";\n");

            string strSql = "SHOW full COLUMNS FROM " + database + "." + table + "";
            DataTable dt = MysqlHelper.GetDataTable(strSql);

            string strFields = "";
            string strValues = "";

            foreach (DataRow dr in dt.Rows)
            {
                string field = dr["field"].ToString();
                string type = dr["type"].ToString();
                string nullvalue = dr["null"].ToString();
                string key = dr["key"].ToString();
                string extra = dr["extra"].ToString();
                string comment = dr["comment"].ToString();

                strValues += "('" + table + "','" + field + "','" + comment + "')" + ",";
            }

            //strFields = strFields.Substring(0, strFields.Length - 1);
            strValues = strValues.Substring(0, strValues.Length - 1);

            strTrans.Append("insert into table_detail(table_name,`field`,name) values" + strValues + "\n");
            //strTrans.Append("update  `" + database + "`.`" + table + "`set " + strFields + "");
            this.richTextBoxexcute.Text = strTrans.ToString();
            return;
        }
    }

    private void panel1_Paint(object sender, PaintEventArgs e)
    {
    }

    private void buttonsave_Click(object sender, EventArgs e)
    {
        savexml();
    }

    //class aa{
    //    public void a() {
    //    var a=ou
    //    }
    //}
    private void savexml()
    {
        string server = this.textBoxServer.Text;
        string user = this.textBoxUid.Text;
        string password = this.textBoxPass.Text;
        string type = this.textBox_type.Text;
        string shujuku = this.textBoxshujuku.Text;

        Hashtable hashtable = new Hashtable();
        hashtable["server"] = server;
        hashtable["user"] = user;
        hashtable["password"] = password;
        hashtable["type"] = type;
        hashtable["shujuku"] = shujuku;

        dbo dboone;
        dboone = dbofactory.getdbo(type);
        dboone.xmlsave(hashtable);

        loadxml();
    }

    private void buttondel_Click(object sender, EventArgs e)
    {
        xmldel();
    }

    private void xmldel()
    {
        string strPath = Application.StartupPath + "\\Config" + "\\sql.xml";
        XmlDocument doc = new XmlDocument();
        doc.Load(strPath);

        XmlNodeList nodeList = doc.SelectSingleNode("root").ChildNodes;

        string server = this.textBoxServer.Text;
        string user = this.textBoxUid.Text;
        string password = this.textBoxPass.Text;
        string type = this.textBox_type.Text;

        var rootnode = doc.SelectSingleNode("root");

        foreach (XmlNode xn in nodeList)
        {
            if (xn.Name == type)
            {
                if (xn.Attributes["server"] == null) { rootnode.RemoveChild(xn); }
                if (xn.Attributes["server"] != null && xn.Attributes["server"].FirstChild.Value == server
                    && xn.Attributes["user"].FirstChild.Value == user)
                {
                    rootnode.RemoveChild(xn);
                    break;
                }
            }
        }

        doc.Save(strPath);
        loadxml();
    }

    protected override bool ProcessCmdKey(ref Message msg, Keys keyData)
    {
        KeyEventArgs e = new KeyEventArgs(keyData);
        if (keyData == (Keys.F5))
        {
            //do something
            sqlrun();
        }
        else if (keyData == (Keys.F1))
        {
            add();
        }
        else if (keyData == (Keys.F2))
        {
            delete();
        }
        else if (keyData == (Keys.F3))
        {
            modify();
        }
        else if (keyData == (Keys.F4))
        {
            read();
        }
        return false;
    }

    private void dataGridView1_SelectionChanged(object sender, EventArgs e)
    {
        if (this.dataGridView1.SelectedRows.Count != 0)
        {
            this.textBoxServer.Text = this.dataGridView1.SelectedRows[0].Cells["server"].Value == null ? "" : this.dataGridView1.SelectedRows[0].Cells["server"].Value.ToString();
            this.textBoxUid.Text = this.dataGridView1.SelectedRows[0].Cells["user"].Value == null ? "" : this.dataGridView1.SelectedRows[0].Cells["user"].Value.ToString();
            this.textBoxPass.Text = this.dataGridView1.SelectedRows[0].Cells["password"].Value == null ? "" : this.dataGridView1.SelectedRows[0].Cells["password"].Value.ToString();
            this.textBox_type.Text = this.dataGridView1.SelectedRows[0].Cells["type"].Value == null ? "" : this.dataGridView1.SelectedRows[0].Cells["type"].Value.ToString();
            this.textBoxshujuku.Text = this.dataGridView1.SelectedRows[0].Cells["db"].Value == null ? "" : this.dataGridView1.SelectedRows[0].Cells["db"].Value.ToString();
            str_type = this.textBox_type.Text;
        }
    }
}